package com.ims.repository;

import com.ims.entity.StudentInfoEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;


public interface StudentInfoRepository extends JpaRepository<StudentInfoEntity, String>{
    @Query("select distinct(s.major) from StudentInfoEntity s order by s.major asc")
    List<String> findMajor();

    @Query("select distinct(s.stuclass) from StudentInfoEntity s where s.major like ?1 order by s.stuclass asc")
    List<String> findStuclassByMajor(String Major);

    @Query("select distinct(s.sex) from StudentInfoEntity s where s.major like ?1 and s.stuclass like ?2")
    List<String> findSex(String Major, String Stuclass);

    @Query("select distinct(s.nation) from StudentInfoEntity s where s.major like ?1 and s.stuclass like ?2 order by s.nation asc")
    List<String> findNation(String Major, String Stuclass);

    @Query("select distinct(s.hometown) from StudentInfoEntity s where s.major like ?1 and s.stuclass like ?2 order by s.hometown asc")
    List<String> findHometown(String Major, String Stuclass);

    @Query("select distinct(s.dorm) from StudentInfoEntity s where s.major like ?1 and s.stuclass like ?2 order by s.dorm asc")
    List<String> findDorm(String Major, String Stuclass);

    @Query("select count(s) from StudentInfoEntity s where s.sex = '男' and s.stuclass = ?1")
    Long findMenStudents(String Stuclass);

    @Query("select count(s) from StudentInfoEntity s where s.sex = '女' and s.stuclass = ?1")
    Long findWomenStudents(String Stuclass);

    @Query("select count(s) from StudentInfoEntity s where s.stuclass = ?1 and s.hometown = ?2")
    Long findNumByStuclassAndHometown(String Stuclass, String Hometown);

    @Query("select s.hometown from StudentInfoEntity s where s.stuclass = ?1")
    List<String> findHometownByStuclass(String Stuclass);
}
